using MySqlConnector;
using MultiAgentsServer.Scripts.AgentTemplates;
using System.Data;

namespace MultiAgentsServer.Scripts.Database;

public class DatabaseInitializer
{
    private readonly string _connectionString;
    private readonly IConfigurationSection _mcConfig;

    private static class DatabaseSchema
    {
        public static readonly Dictionary<string, List<(string ColumnName, string ColumnType)>> ExpectedStructures = new()
        {
            {
                "ManagerUsers", new List<(string, string)>()
                {
                    ("Id", "INT AUTO_INCREMENT PRIMARY KEY"),
                    ("Name", "VARCHAR(255) NOT NULL"),
                    ("Password", "VARCHAR(255) NOT NULL"),
                    ("Salt", "VARCHAR(255) NOT NULL"),
                    ("InvitationCode", "VARCHAR(255) DEFAULT ''")
                }
            },
            {
                "Users", new List<(string, string)>()
                {
                    ("Id", "INT AUTO_INCREMENT PRIMARY KEY"),
                    ("Name", "VARCHAR(255) NOT NULL"),
                    ("Password", "VARCHAR(255) NOT NULL"),
                    ("Salt", "VARCHAR(255) NOT NULL"),
                    ("AvatarUrl", "VARCHAR(255) DEFAULT './'"),
                    ("LastLoginTime", "DATETIME"),
                    ("SelectedChatRoomId", "INT"),
                    ("SelectedProjectId", "INT"),
                    ("AccessChatModels", "JSON"),
                    ("AccessWorkflowModels", "JSON"),
                    ("AllowToolCall", "BOOLEAN DEFAULT false NOT NULL"),
                    ("INDEX", "idx_selectedChatRoomId (SelectedChatRoomId)"),
                    ("INDEX", "idx_selectedProjectId (SelectedProjectId)")
                }
            },
            {
                "WorkflowConfigs", new List<(string, string)>()
                {
                    ("Id", "INT AUTO_INCREMENT PRIMARY KEY"),
                    ("OwnerId", "INT"),
                    ("Name", "VARCHAR(255) NOT NULL"),
                    ("WorkflowJson", "JSON"),
                    ("INDEX", "idx_ownerId (OwnerId)"),
                    ("FOREIGN KEY", "(OwnerId) REFERENCES Users(Id) ON DELETE CASCADE")
                }
            },
            {
                "Projects", new List<(string, string)>()
                {
                    ("Id", "INT AUTO_INCREMENT PRIMARY KEY"),
                    ("Name", "VARCHAR(255) NOT NULL"),
                    ("CreateTime", "DATETIME"),
                    ("RefreshTime", "DATETIME"),
                    ("Status", "INT DEFAULT 0"),
                    ("Setting", "TEXT"),
                    ("UserId", "INT"),
                    ("INDEX", "dx_userId (UserId)"),
                    ("FOREIGN KEY", "(UserId) REFERENCES Users(Id) ON DELETE CASCADE")
                }
            },
            {
                "ChatRooms", new List<(string, string)>()
                {
                    ("Id", "INT AUTO_INCREMENT PRIMARY KEY"),
                    ("Name", "VARCHAR(255) NOT NULL"),
                    ("AIRenamed", "BOOLEAN"),
                    ("ProjectId", "INT"),
                    ("LastMessageId", "INT DEFAULT 0"),
                    ("Status", "INT DEFAULT 0"),
                    ("INDEX", "idx_projectId (ProjectId)"),
                    ("FOREIGN KEY", "(ProjectId) REFERENCES Projects(Id) ON DELETE CASCADE")
                }
            },
            {
                "AgentTemplates", new List<(string, string)>()
                {
                    ("Id", "INT AUTO_INCREMENT PRIMARY KEY"),
                    ("Name", "VARCHAR(255) NOT NULL"),
                    ("UserId", "INT DEFAULT -1"),
                    ("LLMModel", "VARCHAR(255) NOT NULL"),
                    ("Temperature", "FLOAT"),
                    ("AllowedTools", "JSON"),
                    ("PresetMessage", "TEXT"),
                    ("FOREIGN KEY", "(UserId) REFERENCES Users(Id) ON DELETE CASCADE")
                }
            },
            {
                "AgentInstances", new List<(string, string)>()
                {
                    ("Id", "INT AUTO_INCREMENT PRIMARY KEY"),
                    ("ChatRoomId", "INT"),
                    ("TypeId", "INT"),
                    ("LLMModel", "VARCHAR(255) DEFAULT 'Gpt_4_turbo_preview'"),
                    ("Temperature", "FLOAT"),
                    ("AllowedTools", "JSON"),
                    ("INDEX", "idx_chatRoomId (ChatRoomId)"),
                    ("FOREIGN KEY", "(ChatRoomId) REFERENCES ChatRooms(Id) ON DELETE CASCADE"),
                    ("FOREIGN KEY", "(TypeId) REFERENCES AgentTemplates(Id) ON DELETE CASCADE")
                }
            },
            {
                "Configurations", new List<(string, string)>()
                {
                    ("ConfigKey", "VARCHAR(255) PRIMARY KEY"),
                    ("Value", "VARCHAR(255) NOT NULL")
                }
            },
            {
                "UserSubscriptions", new List<(string, string)>()
                {
                    ("Id", "INT AUTO_INCREMENT PRIMARY KEY"),
                    ("IsActive", "BOOLEAN"),
                    ("UserId", "INT"),
                    ("WorkflowId", "INT UNIQUE"),
                    ("Address", "VARCHAR(255) NOT NULL"),
                    ("Class", "INT"),
                    ("ShouldAskMe", "BOOLEAN"),
                    ("FOREIGN KEY", "(UserId) REFERENCES Users(Id) ON DELETE CASCADE"),
                    ("FOREIGN KEY", "(WorkflowId) REFERENCES WorkflowConfigs(Id) ON DELETE CASCADE")
                }
            },
            {
                "SystematicNotifications", new List<(string, string)>()
                {
                    ("Id", "INT AUTO_INCREMENT PRIMARY KEY"),
                    ("SubscriptionId", "INT"),
                    ("UserId", "INT"),
                    ("ContentJSON", "JSON"),
                    ("IsProcessed", "BOOLEAN"),
                    ("FOREIGN KEY", "(UserId) REFERENCES Users(Id) ON DELETE CASCADE"),
                    ("FOREIGN KEY", "(SubscriptionId) REFERENCES UserSubscriptions(Id)")
                }
            },
            {
                "Files", new List<(string, string)>()
                {
                    ("Id", "INT AUTO_INCREMENT PRIMARY KEY"),
                    ("FileName", "VARCHAR(255) NOT NULL"),
                    ("Content", "LONGTEXT"),
                }
            },
        };
    }

    public DatabaseInitializer(IConfiguration configuration)
    {
        IConfigurationSection dbConfig = configuration.GetSection("DatabaseConfig");
        _mcConfig = configuration.GetSection("ManagerCredentials");
        _connectionString =
            $"server={dbConfig["Server"]};" +
            $"port={dbConfig["Port"]};" +
            $"user={dbConfig["User"]};" +
            $"password={dbConfig["Password"]};" +
            $"database={dbConfig["Database"]};";
    }

    public void Initialize()
    {
#if LOCALDEV
        //DeleteDatabaseIfExists();
#endif
        CreateDatabaseIfNotExists();
        CreateTablesIfNotExists();
        InitData();
        ChangeTables();
    }

    private void CreateDatabaseIfNotExists()
    {
        MySqlConnectionStringBuilder builder = new(_connectionString);
        string databaseName = builder.Database;
        string masterConnectionString = builder.ConnectionString;
        masterConnectionString = masterConnectionString.Replace(builder.Database, "mysql");

        using MySqlConnection connection = new(masterConnectionString);
        connection.Open();
        string sql = $"CREATE DATABASE IF NOT EXISTS `{databaseName}`";
        using MySqlCommand command = new(sql, connection);
        command.ExecuteNonQuery();
    }

    public void CreateTablesIfNotExists()
    {
        using var connection = new MySqlConnection(_connectionString);
        connection.Open();

        foreach (var table in DatabaseSchema.ExpectedStructures)
        {
            var tableName = table.Key;
            var tableStructure = table.Value;

            if (!TableExists(connection, tableName))
            {
                CreateTable(connection, tableName, tableStructure);
            }
        }
    }

    private static bool TableExists(MySqlConnection connection, string tableName)
    {
        var sql = $"SHOW TABLES LIKE '{tableName}'";
        using var command = new MySqlCommand(sql, connection);
        using var reader = command.ExecuteReader();
        return reader.HasRows;
    }

    private static void CreateTable(MySqlConnection connection, string tableName, List<(string ColumnName, string ColumnType)> tableStructure)
    {
        var columns = tableStructure.Select(column => $"{column.ColumnName} {column.ColumnType}")
                                    .Aggregate((current, next) => $"{current}, {next}");
        var sql = $"CREATE TABLE {tableName} ({columns})";
        using var command = new MySqlCommand(sql, connection);
        command.ExecuteNonQuery();
    }

    private void InitData()
    {
        using var connection = new MySqlConnection(_connectionString);
        connection.Open();

        var initializeCurrentMessageTableNumberSql = @"
        INSERT INTO Configurations (ConfigKey, Value)
        VALUES ('CurrentMessageTableNumber', '1')
        ON DUPLICATE KEY UPDATE Value = VALUES(Value);";

        var initializeMessageTableSql = @"
        CREATE TABLE IF NOT EXISTS Messages_1 (
            Id INT AUTO_INCREMENT PRIMARY KEY,
            UserId INT,
            AgentId INT,
            Role VARCHAR(255) NOT NULL,
            Author VARCHAR(255) NOT NULL,
            Content MEDIUMTEXT,
            ImageList TEXT,
            TextFileList TEXT,
            HistoryMessagesIdList TEXT,
            ToolCallId TINYTEXT,
            ToolCalls MEDIUMTEXT,
            ChatRoomId INT,
            Time DATETIME,
            INDEX idx_messageId (Id),
            FOREIGN KEY (ChatRoomId) REFERENCES ChatRooms(Id),
            FOREIGN KEY (UserId) REFERENCES Users(Id),
            FOREIGN KEY (AgentId) REFERENCES AgentInstances(Id) 
        )";

        string[] tableCreationScripts =
        [
            initializeCurrentMessageTableNumberSql,
            initializeMessageTableSql,
        ];

        foreach (string script in tableCreationScripts)
        {
            using var command = new MySqlCommand(script, connection);
            command.ExecuteNonQuery();
        }

        SeedAgents();
    }

    private void SeedAgents()
    {
        using var connection = new MySqlConnection(_connectionString);
        connection.Open();

        //InsertOrUpdateAgent(connection, DefaultAgent.Id, DefaultAgent.Name, DefaultAgent.LLMModel, DefaultAgent.Temperature, DefaultAgent.AllowedToolsJson, DefaultAgent.PresetMessage);
        //InsertOrUpdateAgent(connection, GitOperator.Id, GitOperator.Name, GitOperator.LLMModel, GitOperator.Temperature, GitOperator.AllowedToolsJson, GitOperator.PresetMessage);
        //InsertOrUpdateAgent(connection, OctoHelper.Id, OctoHelper.Name, OctoHelper.LLMModel, OctoHelper.Temperature, OctoHelper.AllowedToolsJson, OctoHelper.PresetMessage);

        static void InsertOrUpdateAgent(MySqlConnection connection, int id, string name, string llmModel, float temperature, string allowedTools, string presetMessage)
        {
            var sql = @"
INSERT INTO AgentTemplates (id, Name, LLMModel, Temperature, AllowedTools, PresetMessage)
VALUES (@Id, @Name, @LLMModel, @Temperature, @AllowedTools, @PresetMessage)
ON DUPLICATE KEY UPDATE 
Name=VALUES(Name), LLMModel=VALUES(LLMModel), Temperature=VALUES(Temperature), AllowedTools=VALUES(AllowedTools), PresetMessage=VALUES(PresetMessage);";

            using var command = new MySqlCommand(sql, connection);
            command.Parameters.AddWithValue("@Id", id);
            command.Parameters.AddWithValue("@Name", name);
            command.Parameters.AddWithValue("@LLMModel", llmModel);
            command.Parameters.AddWithValue("@Temperature", temperature);
            command.Parameters.AddWithValue("@AllowedTools", allowedTools);
            command.Parameters.AddWithValue("@PresetMessage", presetMessage);

            command.ExecuteNonQuery();
        }
    }

    private void ChangeTables()
    {
        try
        {
            ExecuteSql("INSERT INTO Users (Id, Name, Password, Salt, AllowToolCall) VALUES (-1, '', '', '', 0);");
            AddColumnIfNotExists("ChatRooms", "Status", "INT DEFAULT 0");

            string insertDataQuery = @$"DELETE FROM ManagerUsers WHERE Name=1;";

            using var connection = new MySqlConnection(_connectionString);
            connection.Open();

            using var command = new MySqlCommand(insertDataQuery, connection);
            command.ExecuteNonQuery();

            connection.Close();
        }
        catch (Exception)
        {
        }

        AddColumnIfNotExists("ChatRooms", "Status", "INT DEFAULT 0");
        AddColumnIfNotExists("ChatRooms", "LastMessageId", "INT DEFAULT 0");
        AddColumnIfNotExists("Projects", "Status", "INT DEFAULT 0");
        DropColumnIfExists("Messages_1", "Tokens");
        AddColumnIfNotExists("Messages_1", "ImageList", "Text");
        AddColumnIfNotExists("Messages_1", "TextFileList", "Text");
        AddColumnIfNotExists("Messages_1", "HistoryMessagesIdList", "Text");
        AddColumnIfNotExists("Users", "AllowToolCall", "BOOLEAN DEFAULT false NOT NULL");
        AddColumnIfNotExists("AgentTemplates", "UserId", "INT DEFAULT 0");
        
        DropColumnIfExists("ChatRooms", "AllowToolCall");

        ExecuteSql("UPDATE AgentTemplates set UserId=-1 where Id=1;");
        ExecuteSql("UPDATE AgentTemplates set UserId=-1 where Id=2;");
        ExecuteSql("UPDATE AgentTemplates set UserId=-1 where Id=3;");

        AddForeignKeyIfNotExists("AgentTemplates", "UserId", "FK_Orders_Users", "(UserId) REFERENCES Users(Id) ON DELETE CASCADE");
    }

    public void ExecuteSql(string sql)
    {
        using var connection = new MySqlConnection(_connectionString);
        connection.Open();

        using var checkCommand = new MySqlCommand(sql, connection);
        object? result = checkCommand.ExecuteScalar();

        connection.Close();
    }

    public void AddForeignKeyIfNotExists(string tableName, string columnName, string foreignKeyName, string foreignKeyDefinition)
    {
        string checkForeignKeyExistsQuery = $@"
    SELECT COUNT(*) 
    FROM information_schema.TABLE_CONSTRAINTS 
    WHERE CONSTRAINT_SCHEMA = DATABASE() 
    AND TABLE_NAME = '{tableName}'
    AND CONSTRAINT_NAME = '{foreignKeyName}'
    AND CONSTRAINT_TYPE = 'FOREIGN KEY';";

        string addForeignKeyQuery = $@"
        ALTER TABLE {tableName} 
        ADD CONSTRAINT {foreignKeyName} FOREIGN KEY {foreignKeyDefinition};";

        using var connection = new MySqlConnection(_connectionString);
        connection.Open();

        using var checkCommand = new MySqlCommand(checkForeignKeyExistsQuery, connection);
        object? result = checkCommand.ExecuteScalar();
        bool foreignKeyExists = Convert.ToInt32(result) > 0;

        if (!foreignKeyExists)
        {
            using var addForeignKeyCommand = new MySqlCommand(addForeignKeyQuery, connection);
            addForeignKeyCommand.ExecuteNonQuery();
        }

        connection.Close();
    }


    public void AddColumnIfNotExists(string tableName, string columnName, string columnDefinition)
    {
        string checkColumnExistsQuery = $@"
        SELECT COUNT(*) 
        FROM information_schema.COLUMNS 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME = '{tableName}'
        AND COLUMN_NAME = '{columnName}'; ";

        string addColumnQuery = $@"
            ALTER TABLE {tableName} 
            ADD COLUMN {columnName} {columnDefinition};";

        using var connection = new MySqlConnection(_connectionString);
        connection.Open();

        using var checkCommand = new MySqlCommand(checkColumnExistsQuery, connection);
        object? result = checkCommand.ExecuteScalar();
        bool columnExists = Convert.ToInt32(result) > 0;

        if (!columnExists)
        {
            using var addColumnCommand = new MySqlCommand(addColumnQuery, connection);
            addColumnCommand.ExecuteNonQuery();
        }

        connection.Close();
    }

    public void DropColumnIfExists(string tableName, string columnName)
    {
        string checkColumnExistsQuery = $@"
        SELECT COUNT(*) 
        FROM information_schema.COLUMNS 
        WHERE TABLE_SCHEMA = DATABASE() 
        AND TABLE_NAME = '{tableName}' 
        AND COLUMN_NAME = '{columnName}';";

        string dropColumnQuery = $@"
            ALTER TABLE {tableName} 
            DROP COLUMN {columnName};";

        using var connection = new MySqlConnection(_connectionString);
        connection.Open();

        using var checkCommand = new MySqlCommand(checkColumnExistsQuery, connection);
        object? result = checkCommand.ExecuteScalar();
        bool columnExists = Convert.ToInt32(result) > 0;

        if (columnExists)
        {
            using var dropColumnCommand = new MySqlCommand(dropColumnQuery, connection);
            dropColumnCommand.ExecuteNonQuery();
        }

        connection.Close();
    }

#if LOCALDEV
    private void DeleteDatabaseIfExists()
    {
        var builder = new MySqlConnectionStringBuilder(_connectionString);
        var databaseName = builder.Database;
        var masterConnectionString = builder.ConnectionString;
        masterConnectionString = masterConnectionString.Replace(builder.Database, "mysql");

        using var connection = new MySqlConnection(masterConnectionString);
        connection.Open();
        var sql = $"DROP DATABASE IF EXISTS `{databaseName}`";
        using var command = new MySqlCommand(sql, connection);
        command.ExecuteNonQuery();
    }
#endif
}

